
const connection = require("./db_utils");
var sqls = {
    // login
    login: 'select * from user where user_name = ? and password = ?',
    // register
    register: 'insert into user (user_name,password,create_time) value (?,?,?)',
    // query user info
    loginUser:'select * from user where user_name  = ?',
    // query content by page
    queryContent: "select DATE_FORMAT(create_time,'%Y-%m-%d %T') as create_time,DATE_FORMAT(update_time,'%Y-%m-%d %T') as update_time, title, category_id, content, user_id from content where user_id = ? order by create_time desc limit ?,?",
    // query content count
    contentConut: 'select count(*) as total from content where user_id = ?',
    // add content
    addContent: 'insert into content (title, category_id, content, user_id, create_time, update_time) value (?,?,?,?,?,?)',
    // update content
    updateContent: 'update content set title = ?,content=?, update_time=? where user_id = ? and id = ?',
    // delete content
    deleteContent: 'delete from content where user_id=? and id = ?'
    

}
//execute sql
var conn_query = function (req, res, sql, params) {
    connection.query(sql, params, function (err, result) {
        if (err) return res.json({code: 500, message: err})
        console.log('conn_query data:',req.data)
        return res.json({code: 200,message: 'success',data: result})
    })
};
//query by page
var get_data_conn = function (req, res, sql, params, sub_sql, sub_params) {
    connection.query(sub_sql, params, function (err, among) {
        if (err) return res.json({code: 500, message: err})
        let total = among[0]['total']
        connection.query(sql, params, function (err, result) {
            if (err) return res.json({code: 500, message: err})
            return res.json({
                code: 200,
                message: 'success',
                data: result,
                paging: {
                    page: parseInt(sub_params[0]),
                    size: parseInt(sub_params[1]),
                    total: total
                }
            })
        })
    })
    
}
module.exports = {
    sqls,
    conn_query,
    get_data_conn

}